USE [CustomersSuppliers]
/****** Object:  StoredProcedure [dbo].[Workers-Insert]    Script Date: [[20120914065003]] ******/
-- =============================================
-- Author:		Ahmed Fathi Hamouda Abd El Hafez
-- Create date: [[20120914065003]]
-- Description:	Insert record into table [dbo].[Workers]
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*IF EXISTS (SELECT * FROM dbo.Sysobjects where id = object_id(N'[dbo].[Workers-Insert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure [dbo].[Workers-Insert]*/
GO
-- =============================================
Create PROCEDURE [dbo].[Workers-Insert]
	/*@Worker_RowNumber int =Null OUTPUT , 
	@Worker_ID uniqueidentifier =Null  , 
	@Worker_Code varchar(50) =Null  , 
	@Worker_NameAr varchar(300) =Null  , 
	@Worker_NameEn varchar(300) =Null  , 
	@Worker_ResidenceDate datetime =Null  , 
	@Worker_ResidenceNumber decimal(20,0)  =Null  , 
	@MaritalStatus_ID uniqueidentifier =Null  , 
	@HaveFamily tinyint =0  , 
	@Job_ID uniqueidentifier =Null  , 
	@HaveExternalGuaranty tinyint =0  , 
	@Worker_Notes varchar(max) =Null  , 
	@Worker_Search varchar(max) =Null  , 
	@Worker_TimeStamp timestamp =Null OUTPUT*/
AS
BEGIN
	--SET NOCOUNT ON added to prevent extra result sets from
	--interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT  INTO [dbo].[Workers](/*[Worker_ID], [Worker_Code], [Worker_NameAr], [Worker_NameEn], [Worker_ResidenceDate], [Worker_ResidenceNumber], [MaritalStatus_ID], [HaveFamily], [Job_ID], [HaveExternalGuaranty], [Worker_Notes], [Worker_Search]*/)
	VALUES     (/*@Worker_ID, @Worker_Code, @Worker_NameAr, @Worker_NameEn, @Worker_ResidenceDate, @Worker_ResidenceNumber, @MaritalStatus_ID, @HaveFamily, @Job_ID, @HaveExternalGuaranty, @Worker_Notes, @Worker_Search*/)

	/*SELECT @Worker_RowNumber = [Worker_RowNumber], @Worker_TimeStamp = [Worker_TimeStamp]
    FROM [dbo].[Workers]
    WHERE  ( [Worker_ID] =@Worker_ID )
	AND  ( [Worker_Code] =@Worker_Code )
	AND  ( [Worker_NameAr] =@Worker_NameAr )
	AND  ( [Worker_NameEn] =@Worker_NameEn )
	AND  ( [Worker_ResidenceDate] =@Worker_ResidenceDate )
	AND  ( [Worker_ResidenceNumber] =@Worker_ResidenceNumber )
	AND  ( [MaritalStatus_ID] =@MaritalStatus_ID )
	AND  ( [HaveFamily] =@HaveFamily )
	AND  ( [Job_ID] =@Job_ID )
	AND  ( [HaveExternalGuaranty] =@HaveExternalGuaranty )
	AND  ( [Worker_Notes] =@Worker_Notes )
	AND  ( [Worker_Search] =@Worker_Search )*/
END
GO
-- =============================================
USE [CustomersSuppliers]
/****** Object:  StoredProcedure [dbo].[Workers-update]    Script Date: [[20120914065003]] ******/
-- =============================================
-- Author:		Ahmed Fathi Hamouda Abd El Hafez
-- Create date: [[20120914065003]]
-- Description:	Update record in table [dbo].[Workers]
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*IF EXISTS (SELECT * FROM dbo.Sysobjects where id = object_id(N'[dbo].[Workers-update]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure [dbo].[Workers-update]*/
GO
-- =============================================
Create PROCEDURE [dbo].[Workers-update]
	/*@Worker_RowNumber int =Null OUTPUT , 
	@Worker_ID uniqueidentifier =Null  , 
	@Worker_Code varchar(50) =Null  , 
	@Worker_NameAr varchar(300) =Null  , 
	@Worker_NameEn varchar(300) =Null  , 
	@Worker_ResidenceDate datetime =Null  , 
	@Worker_ResidenceNumber decimal(20,0)  =Null  , 
	@MaritalStatus_ID uniqueidentifier =Null  , 
	@HaveFamily tinyint =0  , 
	@Job_ID uniqueidentifier =Null  , 
	@HaveExternalGuaranty tinyint =0  , 
	@Worker_Notes varchar(max) =Null  , 
	@Worker_Search varchar(max) =Null  , 
	@Worker_TimeStamp timestamp =Null OUTPUT , 
	@OldTimeStamp timeStamp OUTPUT*/
AS
BEGIN
	--SET NOCOUNT ON added to prevent extra result sets from
	--interfering with SELECT statements.
	SET NOCOUNT ON;

	/*SELECT @OldTimeStamp = [Worker_TimeStamp]
    FROM [dbo].[Workers]
    	WHERE ([Worker_ID] = @Worker_ID)

    IF  @OldTimeStamp = @Worker_TimeStamp
    BEGIN*/
		Update [dbo].[Workers] SET 
		/*[Worker_ID] =@Worker_ID, 
		[Worker_Code] =@Worker_Code, 
		[Worker_NameAr] =@Worker_NameAr, 
		[Worker_NameEn] =@Worker_NameEn, 
		[Worker_ResidenceDate] =@Worker_ResidenceDate, 
		[Worker_ResidenceNumber] =@Worker_ResidenceNumber, 
		[MaritalStatus_ID] =@MaritalStatus_ID, 
		[HaveFamily] =@HaveFamily, 
		[Job_ID] =@Job_ID, 
		[HaveExternalGuaranty] =@HaveExternalGuaranty, 
		[Worker_Notes] =@Worker_Notes, 
		[Worker_Search] =@Worker_Search*/
		/*WHERE ([Worker_ID] = @Worker_ID)*/
	/*END*/

	/*SELECT @Worker_RowNumber = [Worker_RowNumber], @Worker_TimeStamp = [Worker_TimeStamp]
    FROM [dbo].[Workers]
	WHERE ([Worker_ID] = @Worker_ID)*/
END
GO
-- =============================================
USE [CustomersSuppliers]
/****** Object:  StoredProcedure [dbo].[Workers-Insert-Update]    Script Date: [[20120914065003]] ******/
-- =============================================
-- Author:		Ahmed Fathi Hamouda Abd El Hafez
-- Create date: [[20120914065003]]
-- Description:	Insert OR Update record into table [dbo].[Workers]
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*IF EXISTS (SELECT * FROM dbo.Sysobjects where id = object_id(N'[dbo].[Workers-Insert-Update]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure [dbo].[Workers-Insert-Update]*/
GO
-- =============================================
Create PROCEDURE [dbo].[Workers-Insert-Update]
	/*@Worker_RowNumber int =Null OUTPUT , 
	@Worker_ID uniqueidentifier =Null  , 
	@Worker_Code varchar(50) =Null  , 
	@Worker_NameAr varchar(300) =Null  , 
	@Worker_NameEn varchar(300) =Null  , 
	@Worker_ResidenceDate datetime =Null  , 
	@Worker_ResidenceNumber decimal(20,0)  =Null  , 
	@MaritalStatus_ID uniqueidentifier =Null  , 
	@HaveFamily tinyint =0  , 
	@Job_ID uniqueidentifier =Null  , 
	@HaveExternalGuaranty tinyint =0  , 
	@Worker_Notes varchar(max) =Null  , 
	@Worker_Search varchar(max) =Null  , 
	@Worker_TimeStamp timestamp =Null OUTPUT , 
	@Inserted bit OUTPUT , 
	@OldTimeStamp timeStamp OUTPUT*/ 
AS
BEGIN
	--SET NOCOUNT ON added to prevent extra result sets from
	--interfering with SELECT statements.
	SET NOCOUNT ON;
	IF NOT EXISTS (SELECT * FROM [dbo].[Workers] 
					/*WHERE ([Worker_ID] = @Worker_ID)*/)
	BEGIN
		
		INSERT  INTO [dbo].[Workers](/*[Worker_ID], [Worker_Code], [Worker_NameAr], [Worker_NameEn], [Worker_ResidenceDate], [Worker_ResidenceNumber], [MaritalStatus_ID], [HaveFamily], [Job_ID], [HaveExternalGuaranty], [Worker_Notes], [Worker_Search]*/)
		VALUES     (/*@Worker_ID, @Worker_Code, @Worker_NameAr, @Worker_NameEn, @Worker_ResidenceDate, @Worker_ResidenceNumber, @MaritalStatus_ID, @HaveFamily, @Job_ID, @HaveExternalGuaranty, @Worker_Notes, @Worker_Search*/)

		SET /*@*/Inserted=1
	END
	ELSE
	BEGIN
		
		/*SELECT @OldTimeStamp = [Worker_TimeStamp]
		FROM [dbo].[Workers]
    	WHERE ([Worker_ID] = @Worker_ID)

		IF  @OldTimeStamp = @Worker_TimeStamp
		BEGIN*/
			Update [dbo].[Workers] SET 
			/*[Worker_ID] =@Worker_ID, 
			[Worker_Code] =@Worker_Code, 
			[Worker_NameAr] =@Worker_NameAr, 
			[Worker_NameEn] =@Worker_NameEn, 
			[Worker_ResidenceDate] =@Worker_ResidenceDate, 
			[Worker_ResidenceNumber] =@Worker_ResidenceNumber, 
			[MaritalStatus_ID] =@MaritalStatus_ID, 
			[HaveFamily] =@HaveFamily, 
			[Job_ID] =@Job_ID, 
			[HaveExternalGuaranty] =@HaveExternalGuaranty, 
			[Worker_Notes] =@Worker_Notes, 
			[Worker_Search] =@Worker_Search*/
			/*WHERE ([Worker_ID] = @Worker_ID)*/
		/*END*/
		
		SET /*@*/Inserted=0
	END

	/*SELECT @Worker_RowNumber = [Worker_RowNumber], @Worker_TimeStamp = [Worker_TimeStamp]
    FROM [dbo].[Workers]
	WHERE ([Worker_ID] = @Worker_ID)*/
END
GO
-- =============================================
USE [CustomersSuppliers]
/****** Object:  StoredProcedure [dbo].[Workers-Delete]    Script Date: [[20120914065003]] ******/
-- =============================================
-- Author:		Ahmed Fathi Hamouda Abd El Hafez
-- Create date: [[20120914065003]]
-- Description:	delete records from table [dbo].[Workers] based on optional Conditions
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*IF EXISTS (SELECT * FROM dbo.Sysobjects where id = object_id(N'[dbo].[Workers-Delete]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure [dbo].[Workers-Delete]*/
GO
-- =============================================
Create PROCEDURE [dbo].[Workers-Delete]
	/*@Worker_RowNumber int =null  , 
	@Worker_ID uniqueidentifier =null  , 
	@Worker_Code varchar(50) =null  , 
	@Worker_NameAr varchar(300) =null  , 
	@Worker_NameEn varchar(300) =null  , 
	@Worker_ResidenceDate datetime =null  , 
	@Worker_ResidenceNumber decimal(20,0)  =null  , 
	@MaritalStatus_ID uniqueidentifier =null  , 
	@HaveFamily tinyint =null  , 
	@Job_ID uniqueidentifier =null  , 
	@HaveExternalGuaranty tinyint =null  , 
	@Worker_Notes varchar(max) =null  , 
	@Worker_Search varchar(max) =null  , 
	@Worker_TimeStamp timestamp =null*/
AS
BEGIN
	--SET NOCOUNT ON added to prevent extra result sets from
	--interfering with SELECT statements.
	SET NOCOUNT ON;

	Delete FROM  [dbo].[Workers]
	WHERE /*( [Worker_RowNumber] = ISNULL(@Worker_RowNumber, [Worker_RowNumber]) )
	AND ( [Worker_ID] = ISNULL(@Worker_ID, [Worker_ID]) )
	AND ( [Worker_Code] like ISNULL(@Worker_Code, [Worker_Code]) )
	AND ( [Worker_NameAr] like ISNULL(@Worker_NameAr, [Worker_NameAr]) )
	AND ( [Worker_NameEn] like ISNULL(@Worker_NameEn, [Worker_NameEn]) )
	AND ( [Worker_ResidenceDate] = ISNULL(@Worker_ResidenceDate, [Worker_ResidenceDate]) )
	AND ( [Worker_ResidenceNumber] = ISNULL(@Worker_ResidenceNumber, [Worker_ResidenceNumber]) )
	AND ( [MaritalStatus_ID] = ISNULL(@MaritalStatus_ID, [MaritalStatus_ID]) )
	AND ( [HaveFamily] = ISNULL(@HaveFamily, [HaveFamily]) )
	AND ( [Job_ID] = ISNULL(@Job_ID, [Job_ID]) )
	AND ( [HaveExternalGuaranty] = ISNULL(@HaveExternalGuaranty, [HaveExternalGuaranty]) )
	AND ( [Worker_Notes] like ISNULL(@Worker_Notes, [Worker_Notes]) )
	AND ( [Worker_Search] like ISNULL(@Worker_Search, [Worker_Search]) )
	AND ( [Worker_TimeStamp] = ISNULL(@Worker_TimeStamp, [Worker_TimeStamp]) )*/
END
GO
-- =============================================
USE [CustomersSuppliers]
/****** Object:  StoredProcedure [dbo].[Workers-Select]    Script Date: [[20120914065003]] ******/
-- =============================================
-- Author:		Ahmed Fathi Hamouda Abd El Hafez
-- Create date: [[20120914065003]]
-- Description:	select records from table [dbo].[Workers] based on optional Conditions
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*IF EXISTS (SELECT * FROM dbo.Sysobjects where id = object_id(N'[dbo].[Workers-Select]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure [dbo].[Workers-Select]*/
GO
-- =============================================
Create PROCEDURE [dbo].[Workers-Select]
	/*@Worker_RowNumber int =null  , 
	@Worker_ID uniqueidentifier =null  , 
	@Worker_Code varchar(50) =null  , 
	@Worker_NameAr varchar(300) =null  , 
	@Worker_NameEn varchar(300) =null  , 
	@Worker_ResidenceDate datetime =null  , 
	@Worker_ResidenceNumber decimal(20,0)  =null  , 
	@MaritalStatus_ID uniqueidentifier =null  , 
	@HaveFamily tinyint =null  , 
	@Job_ID uniqueidentifier =null  , 
	@HaveExternalGuaranty tinyint =null  , 
	@Worker_Notes varchar(max) =null  , 
	@Worker_Search varchar(max) =null  , 
	@Worker_TimeStamp timestamp =null*/
AS
BEGIN
	--SET NOCOUNT ON added to prevent extra result sets from
	--interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM  [dbo].[Workers]
	WHERE /*( [Worker_RowNumber] = ISNULL(@Worker_RowNumber, [Worker_RowNumber]) )
	AND ( [Worker_ID] = ISNULL(@Worker_ID, [Worker_ID]) )
	AND ( [Worker_Code] like ISNULL(@Worker_Code, [Worker_Code]) )
	AND ( [Worker_NameAr] like ISNULL(@Worker_NameAr, [Worker_NameAr]) )
	AND ( [Worker_NameEn] like ISNULL(@Worker_NameEn, [Worker_NameEn]) )
	AND ( [Worker_ResidenceDate] = ISNULL(@Worker_ResidenceDate, [Worker_ResidenceDate]) )
	AND ( [Worker_ResidenceNumber] = ISNULL(@Worker_ResidenceNumber, [Worker_ResidenceNumber]) )
	AND ( [MaritalStatus_ID] = ISNULL(@MaritalStatus_ID, [MaritalStatus_ID]) )
	AND ( [HaveFamily] = ISNULL(@HaveFamily, [HaveFamily]) )
	AND ( [Job_ID] = ISNULL(@Job_ID, [Job_ID]) )
	AND ( [HaveExternalGuaranty] = ISNULL(@HaveExternalGuaranty, [HaveExternalGuaranty]) )
	AND ( [Worker_Notes] like ISNULL(@Worker_Notes, [Worker_Notes]) )
	AND ( [Worker_Search] like ISNULL(@Worker_Search, [Worker_Search]) )
	AND ( [Worker_TimeStamp] = ISNULL(@Worker_TimeStamp, [Worker_TimeStamp]) )*/
END
GO
-- =============================================
